package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import Model.Coach;


public class DBCoach implements IFDBCoach {
	private static Connection con;
	Statement stmt;
	public DBCoach() 
	{
		con = DBCon.getInstance().getDBCon();
	}
	/* (non-Javadoc)
	 * @see DB.IFBDCoach#createCoach(int, java.lang.String, int, int)
	 */
	@Override
	public void createCoach(String name,String address,String ssn, int phone)
	{
		try{
			int chID=GetMax.getMaxId("Select max(chID) from Coach")+1;//Gets the maximum chID from the DB and adds+1 for the next ID
			String query ="INSERT INTO Coach(chID, name, address, ssn, phone)  VALUES('"+chID+"','"+name+"','"+address+"','"+ssn+"','"+phone+"')";
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			}
	}
	/* (non-Javadoc)
	 * @see DB.IFBDCoach#deleteCoach(int)
	 */
	@Override
	public void deleteCoach(int chID)
	{	//chID=1; 
		try{
			String query="DELETE FROM Coach WHERE chID = '"+chID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{e.printStackTrace();
		}
	}
	/* (non-Javadoc)
	 * @see DB.IFBDCoach#upnameCoach(Model.Coach)
	 */
	@Override
	public void updateCoach(Coach g, int choice)
	{ Coach suObj = g;
	boolean exec= false;
	String query="";
			
	
	switch(choice){
	case 0: //Update Name
		query="UPDATE Coach SET "+
				"name ='"+ suObj.getName()+"'"+
			 	  " WHERE chID = '"+ suObj.getchID() + "'";
			 	 exec =true;
			 	 break;
	case 1: //Update Address
		query="UPDATE Coach SET "+
			 	  "address ='"+ suObj.getAddress()+"'"+
			 	  " WHERE chID = '"+ suObj.getchID() + "'";
			 	 exec =true;
			 	 break;
	case 2://Update SSN
		query="UPDATE Coach SET "+
				"ssn ='"+ suObj.getSsn()+"'"+
			 	 " WHERE chID = '"+ suObj.getchID() + "'";
			 	 exec =true;
			 	 break;
	case 3://Update Phone
		query="UPDATE Coach SET "+
				"phone ='"+ suObj.getPhone()+"'"+
			 	 " WHERE chID = '"+ suObj.getchID() + "'";
			 	 exec =true;
			 	 break;

	case 4://Update All
		query="UPDATE Coach SET "+
			 	  "name ='"+ suObj.getName()+"', "+
			 	  "address ='"+ suObj.getAddress() + "'" +
			 	 "ssn ='"+ suObj.getSsn() + "'" +
			 	"phone ='"+ suObj.getPhone() + "'" +
			          " WHERE chID = '"+ suObj.getchID() + "'";
		exec=true;
		break;}
	
	
              
              
              while(exec)
              {exec =false;
            	  try{
            	  Statement stmt = con.createStatement();
            	  stmt.setQueryTimeout(5);
            	  stmt.executeUpdate(query);
            	  stmt.close();
            	  
              }
              catch(Exception e)
              {e.printStackTrace();}
              }
	}
	
	private String buildQuery()
	{
	    String query="SELECT chID, name, address, ssn, phone   FROM Coach";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	/* (non-Javadoc)
	 * @see DB.IFBDCoach#miscWhere()
	 */
	@Override
	public ArrayList<Coach> miscWhere()
	{
            ResultSet results;
	    ArrayList<Coach> list = new ArrayList<Coach>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Coach trObj = new Coach();
		         trObj = buildCoach(results);	
                 list.add(trObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	/* (non-Javadoc)
	 * @see DB.IFBDCoach#findCoach(int)
	 */
	@Override
	public Coach findCoach(int chID)
	{
		ResultSet results;
		Coach trObj = new Coach();
                
	        String query =  buildQuery() + " WHERE chID ="+"'"+chID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            trObj = buildCoach(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Coach was found
                            trObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return trObj;
	}
	private Coach buildCoach(ResultSet results)
    {   Coach suObj = new Coach();
        try{ // the columns from the table glayee  are used
              suObj.setchID(results.getInt("chID"));
              suObj.setName(results.getString("name"));
              suObj.setAddress(results.getString("address"));
              suObj.setSsn(results.getString("ssn"));
              suObj.setPhone(results.getInt("phone"));

              
        }
       catch(Exception e)
       {
           System.out.println("error in building the Coach object");
       }
       return suObj;
}
	
}
